import MySQLdb

conn = MySQLdb.connect (host = "cs.centenary.edu",
                           user = "bikeswipe",
                           passwd = "Tbontb?76",
                           db = "bikeswipe_db")
cursor = conn.cursor ()
cursor.execute ("DROP TABLE IF EXISTS User")
cursor.execute ("""
       CREATE TABLE User
       (
         id     integer(6),
         f_name VARCHAR(18),
	l_name VARCHAR(18),
	username VARCHAR(8),
	primary key (id)
       )
     """)
cursor.execute ("DROP TABLE IF EXISTS Bike")
cursor.execute ("""
       CREATE TABLE Bike
       (
         id     INTEGER(3),
         user_id INTEGER(6),
	checked_date DATE,
	checked_h INTEGER(2),
	checked_m INTEGER(2),
	checked_s INTEGER(2),
	location INTEGER(2),
	rfid VARCHAR(40),
	primary key (id)
       )
     """)
cursor.execute ("DROP TABLE IF EXISTS History")
cursor.execute ("""
       CREATE TABLE History
       (
         id     INTEGER(8),
         user_id integer(6),
	bike_id integer(3),
	checked_date DATE,
	checked_h INTEGER(2),
	checked_m INTEGER(2),
	checked_s INTEGER(2),
	location INTEGER(2),
	type VARCHAR(8),
	primary key (id)
       )
     """)
user_file = open("bikeswipe_users.txt", "r")
for line in user_file.readlines():
	user_list = line.strip().split(",")
	cursor.execute ("""
       INSERT INTO User (id, f_name, l_name, username)
       VALUES 
         ('%s', '%s', '%s', '%s')
     """ % (user_list[0], user_list[1], user_list[2], user_list[3]))
bike_file = open("bikeswipe_bikes.txt", "r")
for line in bike_file.readlines():
	bike_list = line.strip().split(",")
	cursor.execute ("""
       INSERT INTO Bike (id, user_id, checked_date, checked_h, checked_m, checked_s, location, rfid)
       VALUES
         ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')
     """ % (bike_list[0], bike_list[1], bike_list[2], bike_list[3], bike_list[4], bike_list[5], bike_list[6], bike_list[7]))
cursor.close ()
conn.close ()

